package edu.cicese.alz;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Blank;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.openstreetmap.gui.jmapviewer.MapData;
import org.openstreetmap.gui.jmapviewer.MapLabel;
import org.openstreetmap.gui.jmapviewer.MapLine;
import org.openstreetmap.gui.jmapviewer.MapLocation;
import org.openstreetmap.gui.jmapviewer.MapMarkerDot;
import org.openstreetmap.gui.jmapviewer.interfaces.MapMarker;


import java.awt.Color;
import java.io.File;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * Created by: Eduardo Quintana Contreras
 * Date: 16/12/11
 * Time: 12:59 PM
 */
public class FormatData {
	private ResultSet resultSet;
	private int radius = 50; //meters
	private long minTime = 360; //seconds
	private int userID;

	private float[] alphas = new float[]{0,0.1f,0.2f,0.3f,0.4f,0.5f,0.6f,0.7f,0.8f,0.9f,1.0f};

	public FormatData() {
	}

	public FormatData(ResultSet resultSet) {
		this.resultSet = resultSet;
	}

	public void formatGPS() {
		double latitude, longitude, altitude;
		long timestamp;
		int accuracy;
		int userID;
		String gpsData = "name,latitude,longitude,alt\n";

		try {
//			if (resultSet.getInt(1) == 6) {
//			// Correct data
//				while(resultSet.next()) {
//					timestamp = resultSet.getLong(4);
//					timestamp = timestamp < 1325462463000l ? timestamp - 86400000 : timestamp - 172800000;
//
//					userID = resultSet.getInt(1);
//					latitude = resultSet.getDouble(2);
//					longitude = resultSet.getDouble(3);
//					altitude = resultSet.getDouble(5);
//					accuracy = resultSet.getInt(6);
//
//					gpsData += "ID = " + userID + " (" + TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf) + ") A = " + accuracy +
//							"," + latitude + "," + longitude + "," + altitude + "\n";
//				}
//			}
//			else {
			while (resultSet.next()) {
				timestamp = resultSet.getLong(4);

				userID = resultSet.getInt(1);
				latitude = resultSet.getDouble(2);
				longitude = resultSet.getDouble(3);
				altitude = resultSet.getDouble(5);
				accuracy = resultSet.getInt(6);

				gpsData += "ID = " + userID + " (" + TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf) + ") A = " + accuracy +
						"," + latitude + "," + longitude + "," + altitude + "\n";
			}
//			}

			saveDataFile("D:\\_proyectoAlz\\data\\gps.txt", gpsData);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public MapData formatForMapViewer() {
		double latitude, longitude, altitude, auxLatitude = 0, auxLongitude = 0;
		long timestamp, auxTimestamp = 0;
		int counter = 0;
		double speed;
		long time;
		int distance;
		String unit;

		List<MapMarker> mapMarkers = new ArrayList<>();
		List<MapLabel> mapLabels = new ArrayList<>();
		List<MapLine> mapLines = new ArrayList<>();

		try {
			while (resultSet.next()) {
				latitude = resultSet.getDouble(2);
				longitude = resultSet.getDouble(3);
//				altitude = resultSet.getDouble(5);

//				if (resultSet.getInt(1) == 6) {
//					timestamp = resultSet.getLong(4);
//					timestamp = timestamp < 1325462463000l ? timestamp - 86400000 : timestamp - 172800000;
//				}
//				else {
				timestamp = resultSet.getLong(4);
//				}


				mapMarkers.add(new MapMarkerDot(latitude, longitude));
//				mapLabels.add(new MapLabel(TimeUtil.getStringDateFromMs(timestamp) + ", A = " + altitude,
//						latitude, longitude));
				counter++;
				if (counter > 1) {
					distance = (int) GPSUtilities.getSLCDistFrom(auxLatitude, auxLongitude, latitude, longitude);
					time = (timestamp - auxTimestamp) / 1000;
					speed = distance / ((double) time / 60);
					if (speed > 1000) {
						speed /= 1000;
						unit = "km/min";
					} else {
						unit = "m/min";
					}
					String speedf = String.format("%.2f", speed);
					String distancef = String.format("%.1f", distance);
					mapLabels.add(new MapLabel(TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf) + "  " +
							time + "s.  " + distancef + "m.  " + speedf + unit, timestamp + "",
							latitude, longitude));
					mapLines.add(new MapLine(auxLatitude, auxLongitude, latitude, longitude));
//					getDistances(auxLatitude, auxLongitude, latitude, longitude);
				} else {
					mapLabels.add(new MapLabel(new Color(0, 0, 0, 0), new Color(0, 0, 0, 0),
							TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf),
							latitude, longitude));
				}
				auxLatitude = latitude;
				auxLongitude = longitude;
				auxTimestamp = timestamp;
			}

			System.out.println(mapMarkers.size() + " samples.");

			// Calculate locations of interest
			ArrayList<MapLocation> mapLocations = getLocationsOfInterest(mapMarkers);
			System.out.println(mapLocations.size() + " location(s) of interest found.");

			return new MapData(mapMarkers, mapLines, mapLabels, mapLocations);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	private ArrayList<MapLocation> getLocationsOfInterest(List<MapMarker> mapMarkers) {
		double maxDist = 50;
		ArrayList<MarkerCluster> markerClusters = new ArrayList<>();

		for (MapMarker m1 : mapMarkers) {
			MarkerCluster markerCluster = new MarkerCluster(m1);
			for (MapMarker m2 : mapMarkers) {
				if (!m1.equals(m2)) {
					if ((int) GPSUtilities.getSLCDistFrom(m1.getLat(), m1.getLon(), m2.getLat(), m2.getLon())
							<= maxDist) {
						markerCluster.add(m2);
					}
				}
			}
			markerClusters.add(markerCluster);
		}

		// Sort by quantity of markers in the cluster
		Collections.sort(markerClusters);

//		ArrayList<MapLocation> mapLocations = new ArrayList<>();

		return getLocations(markerClusters);


		/*ArrayList<MapMarker> closeMarkers;
		HashMap<MapMarker, ArrayList<MapMarker>> markers = new HashMap<>();

		for (MapMarker m1 : mapMarkers) {
			closeMarkers = new ArrayList<>();
			for (MapMarker m2 : mapMarkers) {
				if (!m1.equals(m2)) {
					if ((int) GPSUtilities.getSLCDistFrom(m1.getLat(), m1.getLon(), m2.getLat(), m2.getLon()) <= 20) {
						closeMarkers.add(m2);
					}
				}
			}
			markers.put(m1, closeMarkers);
		}
		for (Map.Entry<MapMarker, ArrayList<MapMarker>> marker : markers.entrySet()) {

		}*/


	}

	// Calculate locations of interest
	private ArrayList<MapLocation> getLocations(ArrayList<MarkerCluster> markerClusters) {
		ArrayList<MapLocation> mapLocations = new ArrayList<>();
		ArrayList<MapMarker> currentAddedMarkers = new ArrayList<>();
		ArrayList<MapMarker> globalAddedMarkers = new ArrayList<>();
		MapMarker mainMarker;
		int c = 1;

		for (MarkerCluster cluster : markerClusters) {
			// Remove already added markers to another location of interest
			cluster.removeDuplicates(globalAddedMarkers);

			// If remaining markers are at least 10
			if (cluster.getQuantity() >= 10) {
				mainMarker = cluster.getMarker();
//				mapLocations.add(new MapLocation(mainMarker.getLat(), mainMarker.getLon(), cluster.getQuantity(),
//						"(" + c + ")"));
				currentAddedMarkers.add(mainMarker);
				globalAddedMarkers.add(mainMarker);
				for (MapMarker m : cluster.getMarkers()) {
					currentAddedMarkers.add(m);
					globalAddedMarkers.add(m);
				}
				double[] maxmin = getMaxMin(currentAddedMarkers);
				mapLocations.add(new MapLocation(mainMarker.getLat(), mainMarker.getLon(), maxmin[0], maxmin[1],
						maxmin[2], maxmin[3], cluster.getQuantity(), cluster.getQuantity() + ""));
				c++;
				currentAddedMarkers.clear();
			}
		}

		return mapLocations;
	}


	public MapData formatForMapViewerB() {
		double latitude, longitude, altitude, auxLatitude = 0, auxLongitude = 0;
		long timestamp, auxTimestamp = 0;
		int counter = 0;
		double speed;
		long time;
		double distance;
		String unit;

		List<MapMarker> mapMarkers = new ArrayList<>();
		List<MapLabel> mapLabels = new ArrayList<>();
		List<MapLine> mapLines = new ArrayList<>();

		try {
			while (resultSet.next()) {
				latitude = resultSet.getDouble(2);
				longitude = resultSet.getDouble(3);
//				altitude = resultSet.getDouble(5);

//				if (resultSet.getInt(1) == 6) {
//					timestamp = resultSet.getLong(4);
//					timestamp = timestamp < 1325462463000l ? timestamp - 86400000 : timestamp - 172800000;
//				}
//				else {
				timestamp = resultSet.getLong(4);
//				}


				counter++;
				if (counter > 1) {
					distance = GPSUtilities.getSLCDistFrom(auxLatitude, auxLongitude, latitude, longitude);
					time = (timestamp - auxTimestamp) / 1000;
					speed = distance / ((double) time / 60);
					if (speed > 1000) {
						speed /= 1000;
						unit = "km/min";
					} else {
						unit = "m/min";
					}
					String speedf = String.format("%.2f", speed);
					String distancef = String.format("%.1f", distance);
					mapLabels.add(new MapLabel(TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf) + "  " +
							time + "s.  " + distancef + "m.  " + speedf + unit, timestamp + "",
							latitude, longitude));
					mapLines.add(new MapLine(auxLatitude, auxLongitude, latitude, longitude));
				} else {
					distance = 0;
					mapLabels.add(new MapLabel(new Color(0, 0, 0, 0), new Color(0, 0, 0, 0),
							TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf),
							latitude, longitude));
				}

				mapMarkers.add(new MapMarkerDot(latitude, longitude, timestamp, distance));
				auxLatitude = latitude;
				auxLongitude = longitude;
				auxTimestamp = timestamp;
			}

//			System.out.println(mapMarkers.size() + " samples.");

			// Calculate locations of interest
			ArrayList<MapLocation> mapLocations = getLocationsOfInterest(getMarkerClusters(mapMarkers));
//			System.out.println(mapLocations.size() + " location(s) of interest found.");

			return new MapData(mapMarkers, mapLines, mapLabels, mapLocations);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	private ArrayList<MarkerCluster> getMarkerClusters(List<MapMarker> mapMarkers) {
		ArrayList<MarkerCluster> markerClusters = new ArrayList<>();
		int mapMarkersSize = mapMarkers.size();

		for (int i = 0; i < mapMarkersSize; i++) {
			MarkerCluster markerCluster = new MarkerCluster();
			MapMarker m1 = mapMarkers.get(i);
			markerCluster.add(m1);

			// Add previous marker
			if (i != 0) {
				markerCluster.setMarkerA(mapMarkers.get(i - 1));
			}

			// Add the next markers, as long as they are inside the radius specified
			int j;
			for (j = i + 1; j < mapMarkers.size(); j++) {
				MapMarker m2 = mapMarkers.get(j);
				if (GPSUtilities.getSLCDistFrom(m1.getLat(), m1.getLon(), m2.getLat(), m2.getLon()) <= radius) {
					markerCluster.add(m2);
				} else {
					break;
				}
			}

			// Add next marker
			if (j < mapMarkersSize) {
				markerCluster.setMarkerB(mapMarkers.get(j));
			}

			markerClusters.add(markerCluster);
		}

		// Sort by quantity of markers in the cluster
		Collections.sort(markerClusters);

		return markerClusters;
	}

	private ArrayList<MapLocation> getLocationsOfInterest(ArrayList<MarkerCluster> markerClusters) {
		ArrayList<MapLocation> mapLocations = new ArrayList<>();
		ArrayList<MapMarker> currentAddedMarkers = new ArrayList<>();
		ArrayList<MapMarker> globalAddedMarkers = new ArrayList<>();
		long timeSpentInside;

		for (MarkerCluster cluster : markerClusters) {
			// Remove already added markers to another location of interest
			cluster.removeDuplicates(globalAddedMarkers);

			// Calculate user's time spent in the cluster
			if (!cluster.getMarkers().isEmpty()) {
				timeSpentInside = getTimeSpentInArea(cluster);

				// If the user spent more than X time inside the location
//				System.out.println("Spent " + timeSpentInside + " s. " + cluster.getQuantity() + " " + cluster.getMarkers().size());
				if (timeSpentInside >= minTime) {
					currentAddedMarkers.addAll(cluster.getMarkers());
					globalAddedMarkers.addAll(cluster.getMarkers());

					double[] maxMin = getMaxMin(currentAddedMarkers);
//					mapLocations.add(new MapLocation(cluster.getMarkers().get(0).getLat(),
//							cluster.getMarkers().get(0).getLon(), maxMin[0], maxMin[1],
//							maxMin[2], maxMin[3], cluster.getQuantity(), ""));
					MapLocation mapLocation = new MapLocation(maxMin[0], maxMin[1], maxMin[2], maxMin[3],
							maxMin[4], maxMin[5], cluster.getQuantity(), timeSpentInside / 60 + "",
							(int) timeSpentInside / 60);
					mapLocation.setMapLocationID(RouteUtils.getMapLocationCounter());
					mapLocation.addMapMarkers(currentAddedMarkers);
					mapLocations.add(mapLocation);

					currentAddedMarkers.clear();
				}
			}
		}

		return mapLocations;
	}

	private long getTimeSpentInArea(MarkerCluster markerCluster) {
		long timeA = 0, timeB = 0, time;
		ArrayList<MapMarker> mapMarkers = markerCluster.getMarkers();
		MapMarker m0 = mapMarkers.get(0); // first marker in the cluster
		MapMarker mn = mapMarkers.get(mapMarkers.size() - 1); // last marker in the cluster

		if (markerCluster.getMarkerA() != null) {
			timeA = m0.getTimestamp() - markerCluster.getMarkerA().getTimestamp();
			timeA = (long) (timeA / m0.getDistanceToPreviousMarker()) * radius;
		}

		time = mn.getTimestamp() - m0.getTimestamp();

		if (markerCluster.getMarkerB() != null) {
			timeB = markerCluster.getMarkerB().getTimestamp() - mn.getTimestamp();
			timeB = (long) (timeB / markerCluster.getMarkerB().getDistanceToPreviousMarker()) * radius;
//			System.out.println(markerCluster.getMarkerB().getTimestamp() + " " + markerCluster.getMarkerB().getDistanceToPreviousMarker());
		}

//		System.out.println(time/1000 + " " + timeA/1000 + " " + timeB/1000);

//		return (time + timeA + timeB) / 1000;
		return time / 1000;
	}

	private double[] getMaxMin(ArrayList<MapMarker> addedMarkers) {
		double minLat = 90, maxLat = -90;
		double minLon = 180, maxLon = -180;
		double lat, lon;
//		double[] maxmin = new double[4];
		for (MapMarker marker : addedMarkers) {
			lat = marker.getLat();
			lon = marker.getLon();
			if (lat < minLat) {
				minLat = lat;
			}
			if (lat > maxLat) {
				maxLat = lat;
			}
			if (lon < minLon) {
				minLon = lon;
			}
			if (lon > maxLon) {
				maxLon = lon;
			}
		}
		lat = minLat + (maxLat - minLat) / 2;
		lon = minLon + (maxLon - minLon) / 2;
		return new double[]{lat, lon, minLat, minLon, maxLat, maxLon};
	}

	////////////////////////////////////


	public void formatWifi(ResultSet resultSet) {
		long timestamp = 0;
		int userID = 0;
		int level;
		String bssid, ssid;
		String wifiData = "";

		try {
			while (resultSet.next()) {
				userID = resultSet.getInt(1);
				bssid = resultSet.getString(2);
				level = resultSet.getInt(3);
				ssid = resultSet.getString(4);
				timestamp = resultSet.getLong(5);
				wifiData += userID + " - " + TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdf) + " - " + bssid + " - " +
						ssid + " - " + level + "\n";
			}
			String folder = "D:\\_proyectoAlz\\analysis\\users\\incense" + userID + "\\" +
					TimeUtil.getStringDateFromMs(timestamp, TimeUtil.sdfFolder).replace("/", "-");
			new File(folder).mkdirs();
			saveDataFile(folder + "\\wifi.txt", wifiData);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<ActivityMeasure> formatNFC() {
		ArrayList<ActivityMeasure> activities = new ArrayList<>();
		int messageID;
		try {
			while (resultSet.next()) {
				messageID = resultSet.getInt(2);
				activities.add(new ActivityMeasure(messageID + ""/* + ":" + resultSet.getString(1)*/, messageID,
						resultSet.getLong(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			WritableWorkbook workbook = Workbook.createWorkbook(new File("D:\\_proyectoAlz\\analysis\\output_" + SQLQuery.userID + ".xls"));
			WritableSheet worksheet = workbook.createSheet("NFC", 0);


			// Create a cell format for Arial 10 point font
			WritableFont font1 = new WritableFont(WritableFont.createFont("CALIBRI"), 10);
			WritableFont font2 = new WritableFont(WritableFont.createFont("CALIBRI"), 11);
			WritableFont font3 = new WritableFont(WritableFont.createFont("MYRIAD PRO"), 8, WritableFont.BOLD);
			WritableFont font4 = new WritableFont(WritableFont.createFont("CALIBRI"), 11, WritableFont.BOLD);

			WritableCellFormat idFormat = new WritableCellFormat(font4);
			idFormat.setAlignment(Alignment.RIGHT);

			WritableCellFormat activityFormat = new WritableCellFormat(font3);
			activityFormat.setAlignment(Alignment.LEFT);

			for (int i = 1; i < 21; i++) {
				worksheet.addCell(new Label(0, i + 1, i + "", idFormat));
				worksheet.addCell(new Label(1, i + 1, NFCActivity.activities[i - 1], activityFormat));
			}

			worksheet.setColumnView(0, 3);
			worksheet.setColumnView(1, 30);
			worksheet.setColumnView(2, 2);

			DateFormat customDayFormat = new DateFormat("dd/MM/yyyy");
			WritableCellFormat dayFormat = new WritableCellFormat(font1, customDayFormat);
			dayFormat.setAlignment(Alignment.CENTRE);

			DateFormat customTimeFormat = new DateFormat("hh:mm:ss");
			WritableCellFormat timeFormat = new WritableCellFormat(font1, customTimeFormat);

			WritableCellFormat messageFormat = new WritableCellFormat(font2);
			messageFormat.setAlignment(Alignment.CENTRE);

			Date date;
			Calendar calendar = Calendar.getInstance();
			int day, dayAux = -1;
			int column = -1, dayRow = 1, activityRow = 2;
			for (ActivityMeasure activity : activities) {
				date = TimeUtil.getDateFromMs(activity.getTimestamp());
				calendar.setTime(date);
				day = calendar.get(Calendar.DAY_OF_YEAR);
				if (dayAux != day) {
					dayAux = day;
					column += 4;
					activityRow = 2;
					worksheet.mergeCells(column, dayRow, column + 1, dayRow);
					worksheet.mergeCells(column + 2, dayRow, column + 3, dayRow);
					worksheet.addCell(new DateTime(column, dayRow, date, dayFormat));
					worksheet.addCell(new DateTime(column + 2, dayRow, date, dayFormat));
					worksheet.setColumnView(column, 8);
					worksheet.setColumnView(column + 1, 3);
					worksheet.setColumnView(column + 2, 8);
					worksheet.setColumnView(column + 3, 3);

					for (int i = activityRow; i < 50; i++) {
						worksheet.addCell(new Blank(column + 2, i, timeFormat));
						worksheet.addCell(new Blank(column + 3, i, messageFormat));
					}
				}
				DateTime timeCell = new DateTime(column, activityRow, date, timeFormat);
				worksheet.addCell(timeCell);
				Number numberCell = new Number(column + 1, activityRow, activity.getMessageID(), messageFormat);
				worksheet.addCell(numberCell);
				activityRow++;
			}

			workbook.write();
			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return activities;
		}
	}

	public void formatAccelerometer(int userID, ResultSet resultSet, long type, int file) {
		double axisX, axisY, axisZ;
		long timestamp;
		String accData = "axisX,axisY,axisZ,timestamp,userID\n";
		try {
			String folder = "D:\\_proyectoAlz\\analysis\\accelerometer\\" + userID + "\\" + type + "\\";
			new File(folder).mkdirs();
			saveDataFile(folder + "\\" + file + ".txt", accData);

			while (resultSet.next()) {
				axisX = resultSet.getDouble(1);
				axisY = resultSet.getDouble(2);
				axisZ = resultSet.getDouble(3);
				timestamp = resultSet.getLong(4);
				accData = axisX + "," + axisY + "," + axisZ + "," + timestamp + "," + userID + "\n";
				saveDataFile(folder + "\\" + file + ".txt", accData);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void formatAccelerometer(int userID, ResultSet resultSet, int file) {
		ArrayList<AccelerometerMeasure> accelerometerMeasures = new ArrayList<>();
		try {
			while (resultSet.next()) {
				accelerometerMeasures.add(new AccelerometerMeasure(
						resultSet.getDouble(1),
						resultSet.getDouble(2),
						resultSet.getDouble(3),
						resultSet.getLong(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (!accelerometerMeasures.isEmpty()) {
			String folder = "D:\\_proyectoAlz\\analysis\\accelerometer\\walking\\";
			new File(folder).mkdirs();

			try {
				String user = userID < 10 ? "0" + userID : userID + "";
				String fileNumber = file < 10 ? "0" + file : file + "";
				String filename = folder + "user" + user + "w" + fileNumber + ".xls";

				WorkbookSettings wbSetting = new WorkbookSettings();
				wbSetting.setUseTemporaryFileDuringWrite(true);
				WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), wbSetting);

//				FunctionNames fn = ws.getFunctionNames();
//				Formula f =
//
//
//
//                Function f = fn.getFunction(v);

				WritableFont fontNormal = new WritableFont(WritableFont.createFont("CALIBRI"), 11);
				WritableFont fontBold = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD);
				WritableFont fontRed = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
				WritableFont fontRedBig = new WritableFont(WritableFont.createFont("CALIBRI"), 16, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
				WritableFont fontBlue = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);

				WritableCellFormat formatNormal = new WritableCellFormat(fontNormal);
				WritableCellFormat formatNormalDecimal = new WritableCellFormat(fontNormal, new NumberFormat("0.00"));
				WritableCellFormat formatNormalDecimalAlpha = new WritableCellFormat(fontNormal, new NumberFormat("0.0"));
				WritableCellFormat formatNormalTimestamp = new WritableCellFormat(fontNormal, new NumberFormat("0"));
				WritableCellFormat formatBold = new WritableCellFormat(fontBold);
				WritableCellFormat formatBoldDecimal = new WritableCellFormat(fontBold, new NumberFormat("0.00"));
				WritableCellFormat formatRed = new WritableCellFormat(fontRed);
				WritableCellFormat formatRedBig = new WritableCellFormat(fontRedBig, new NumberFormat("0.00"));
				WritableCellFormat formatRedDecimal = new WritableCellFormat(fontRed, new NumberFormat("0.00"));
				WritableCellFormat formatBlue = new WritableCellFormat(fontBlue, new NumberFormat("0.0"));
				WritableCellFormat formatDivisor = new WritableCellFormat();
				formatDivisor.setBackground(Colour.GREY_25_PERCENT);
//			    formatNormal.setAlignment(Alignment.RIGHT);

				float alpha = 0;
				int row = 0;
				for (int  ws = 1; ws <= 11; alpha += 0.1, ws++) {
					WritableSheet worksheet = workbook.createSheet("user" + user + "w" + fileNumber + "_" + String.format("%.1f", alpha), ws);


					worksheet.addCell(new Number(11, 0, alpha, formatBlue));

					row = 2;
					for (AccelerometerMeasure accelerometerMeasure : accelerometerMeasures) {
						worksheet.addCell(new Number(0, row - 1, accelerometerMeasure.getTimestamp(), formatNormalTimestamp));
						worksheet.addCell(new Number(1, row - 1, accelerometerMeasure.getAxisX(), formatNormalDecimal));
						worksheet.addCell(new Number(2, row - 1, accelerometerMeasure.getAxisY(), formatNormalDecimal));
						worksheet.addCell(new Number(3, row - 1, accelerometerMeasure.getAxisZ(), formatNormalDecimal));

						worksheet.addCell(new Formula(4, row - 1, "RAIZ(B" + row + "*B" + row + "+C" + row + "*C" + row +
								"+D" + row + "*D" + row + ")", formatBoldDecimal));
						worksheet.addCell(new Formula(5, row - 1, "REDONDEAR(E" + row + ",0)", formatBold));
						//empty
						if (row > 2) {
							worksheet.addCell(new Formula(7, row - 1, "A" + row + "-A" + (row - 1), formatNormal));
							worksheet.addCell(new Formula(8, row - 1, "I" + (row - 1) + "+H" + row, formatNormal));
							//empty
							worksheet.addCell(new Formula(10, row - 1, "$L$1*K" + (row - 1) + "+(1-$L$1)*B" + row, formatNormalDecimal));
							worksheet.addCell(new Formula(11, row - 1, "$L$1*L" + (row - 1) + "+(1-$L$1)*C" + row, formatNormalDecimal));
							worksheet.addCell(new Formula(12, row - 1, "$L$1*M" + (row - 1) + "+(1-$L$1)*D" + row, formatNormalDecimal));
						} else {
							worksheet.addCell(new Formula(10, row - 1, "$L$1*0+(1-$L$1)*B" + row, formatNormalDecimal));
							worksheet.addCell(new Formula(11, row - 1, "$L$1*0+(1-$L$1)*C" + row, formatNormalDecimal));
							worksheet.addCell(new Formula(12, row - 1, "$L$1*0+(1-$L$1)*D" + row, formatNormalDecimal));
							//$L$1*0+(1-$L$1)*B2
						}
						//empty
						worksheet.addCell(new Formula(14, row - 1, "B" + row + "-K" + row + "", formatNormalDecimal));
						worksheet.addCell(new Formula(15, row - 1, "C" + row + "-L" + row + "", formatNormalDecimal));
						worksheet.addCell(new Formula(16, row - 1, "D" + row + "-M" + row + "", formatNormalDecimal));
						worksheet.addCell(new Formula(17, row - 1, "RAIZ(O" + row + "*O" + row + "+P" + row + "*P" + row +
								"+Q" + row + "*Q" + row + ")", formatBoldDecimal));
						worksheet.addCell(new Formula(18, row - 1, "REDONDEAR(R" + row + ",0)", formatBold));

						worksheet.addCell(new Formula(20, row, "SI(H" + row + ">" + AccelerometerUtil.maxFrequency + ",H" + row + ",\"\")", formatNormal));

						row++;
					}
					worksheet.addCell(new Formula(0, 0, "(A" + (row - 1) + "-A2)/1000", formatRedDecimal));
					worksheet.addCell(new Formula(4, 0, "SUMA(E2:E" + (row - 1) + ")", formatRedDecimal));
					worksheet.addCell(new Formula(5, 0, "SUMA(F2:F" + (row - 1) + ")", formatRed));
					worksheet.addCell(new Formula(17, 0, "SUMA(R2:R" + (row - 1) + ")", formatRedDecimal));
					worksheet.addCell(new Formula(18, 0, "SUMA(S2:S" + (row - 1) + ")", formatRed));
//					worksheet.addCell(new Formula(19, 0, "S1/(A1/2/60)", formatRedBig));

					worksheet.addCell(new Formula(21, 0, "S1/(V2/60)", formatRedBig));
					worksheet.addCell(new Formula(21, 1, "(A" + (row - 1) + "-A2-V4+V5)/1000", formatNormalDecimal));
					worksheet.addCell(new Formula(21, 2, "CONTAR(U2:U" + (row - 1) + ")", formatNormal));
					worksheet.addCell(new Formula(21, 3, "SUMA(U2:U" + (row - 1) + ")", formatNormal));
					worksheet.addCell(new Formula(21, 4, "V3*40", formatNormal));

					int[] columnWidths = new int[]{14, 6, 6, 6, 10, 6, 2, 7, 7, 2, 6, 6, 6, 2, 6, 6, 6, 10, 6, /*11, */2, 2, 11};

					for (int c = 0; c < columnWidths.length; c++) {
						worksheet.setColumnView(c, columnWidths[c]);
					}

					//insert empty cells
					for (int r = 0; r < row - 1; r++) {
						worksheet.addCell(new Label(6, r, "", formatDivisor));
						worksheet.addCell(new Label(9, r, "", formatDivisor));
						worksheet.addCell(new Label(13, r, "", formatDivisor));
						worksheet.addCell(new Label(19, r, "", formatDivisor));
					}

//					workbook.write();
				}

				

//				workbook.write();

				WritableCellFormat formatBoldCenter = new WritableCellFormat(fontBold);
				formatBoldCenter.setAlignment(Alignment.CENTRE);

				WritableSheet worksheet = workbook.createSheet("summary", 0);
				worksheet.addCell(new Label(0, 1, "Alpha", formatBoldCenter));
				worksheet.addCell(new Label(1, 1, "Magnitude", formatBoldCenter));
				worksheet.addCell(new Label(2, 1, "Magnitude [r]", formatBoldCenter));
				worksheet.addCell(new Label(3, 1, "Magnitude [-g]", formatBoldCenter));
				worksheet.addCell(new Label(4, 1, "Magnitude [-g][r]", formatBoldCenter));
//				worksheet.addCell(new Label(5, 1, "Magnitude [-g][r][t]", formatBoldCenter));
				worksheet.addCell(new Label(5, 1, "Magnitude [-g][r][min]", formatBoldCenter));
				worksheet.addCell(new Label(7, 1, "Frequency", formatBoldCenter));

				for (int  i = 0; i <= 10; i++) {
					worksheet.addCell(new Number(0, i + 2, alphas[i], formatNormalDecimalAlpha));
					String wsName = "'user" + user + "w" + fileNumber + "_" + String.format("%.1f", alphas[i]) + "'!";
					worksheet.addCell(new Formula(1, i + 2,	wsName + "E1", formatNormalDecimal));
					worksheet.addCell(new Formula(2, i + 2,	wsName + "F1", formatNormal));
					worksheet.addCell(new Formula(3, i + 2,	wsName + "R1", formatNormalDecimal));
					worksheet.addCell(new Formula(4, i + 2,	wsName + "S1", formatNormal));
//					worksheet.addCell(new Formula(5, i + 2,	wsName + "T1", formatNormalDecimal));
					worksheet.addCell(new Formula(5, i + 2,	wsName + "V1", formatBoldDecimal));
				}
				int[] summaryColumnWidths = new int[]{7, 14, 15, 16, 18, /*20, */22, 2, 11};

				for (int c = 0; c < summaryColumnWidths.length; c++) {
					worksheet.setColumnView(c, summaryColumnWidths[c]);
				}

				//frequency
				//k 10
				for (int r = 3; r < row; r++) {
					worksheet.addCell(new Formula(10, r - 1, "'user" + user + "w" + fileNumber + "_0.0'!H" + r, formatNormal));
					worksheet.addCell(new Formula(11, r - 1, "SI(K" + r + "<" + AccelerometerUtil.maxFrequency + ", K" + r + ", \"\")", formatNormal));
				}
				worksheet.addCell(new Formula(7, 2,	"PROMEDIO(L3:L" + (row - 1) + ")", formatBoldDecimal));

				workbook.write();
				workbook.close();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void formatAccelerometerSimplified2(int userID, ResultSet resultSet, int file) {
		ArrayList<AccelerometerMeasure> accelerometerMeasures = new ArrayList<>();
		try {
			while (resultSet.next()) {
				accelerometerMeasures.add(new AccelerometerMeasure(
						resultSet.getDouble(1),
						resultSet.getDouble(2),
						resultSet.getDouble(3),
						resultSet.getLong(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (!accelerometerMeasures.isEmpty()) {
			String folder = "D:\\_proyectoAlz\\analysis\\accelerometer\\walkingSimple\\";
			new File(folder).mkdirs();

			try {
				String user = userID < 10 ? "0" + userID : userID + "";
				String fileNumber = file < 10 ? "0" + file : file + "";
				String filename = folder + "user" + user + "w" + fileNumber + ".xls";

				WorkbookSettings wbSetting = new WorkbookSettings();
				wbSetting.setUseTemporaryFileDuringWrite(true);
				WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), wbSetting);

				WritableFont fontNormal = new WritableFont(WritableFont.createFont("CALIBRI"), 11);
				WritableCellFormat formatNormalDecimal = new WritableCellFormat(fontNormal, new NumberFormat("0.00"));
				WritableCellFormat formatNormalTimestamp = new WritableCellFormat(fontNormal, new NumberFormat("0"));
				WritableCellFormat formatDivisor = new WritableCellFormat();
				formatDivisor.setBackground(Colour.GREY_25_PERCENT);

				WritableSheet worksheet = workbook.createSheet("user" + user + "w" + fileNumber, 0);

				int row = 0;
				for (AccelerometerMeasure accelerometerMeasure : accelerometerMeasures) {
					worksheet.addCell(new Number(0, row, accelerometerMeasure.getTimestamp(), formatNormalTimestamp));
					worksheet.addCell(new Number(1, row, accelerometerMeasure.getAxisX(), formatNormalDecimal));
					worksheet.addCell(new Number(2, row, accelerometerMeasure.getAxisY(), formatNormalDecimal));
					worksheet.addCell(new Number(3, row, accelerometerMeasure.getAxisZ(), formatNormalDecimal));
					row++;
				}

				int[] columnWidths = new int[]{14, 6, 6, 6};

				for (int c = 0; c < columnWidths.length; c++) {
					worksheet.setColumnView(c, columnWidths[c]);
				}

				workbook.write();
				workbook.close();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void formatAccelerometerSimplified(int userID, ResultSet resultSet, int file) {
		double axisX, axisY, axisZ, magnitude;
		ArrayList<AccelerometerMeasure> accelerometerMeasures = new ArrayList<>();
		double accMagnitudes = 0;
		long accMagnitudesRounded = 0;
		try {
			while (resultSet.next()) {
				axisX = resultSet.getDouble(1);
				axisY = resultSet.getDouble(2);
				axisZ = resultSet.getDouble(3);
				accMagnitudes += magnitude = Math.sqrt(Math.pow(axisX, 2) + Math.pow(axisY, 2) + Math.pow(axisZ, 2));
				accMagnitudesRounded += StrictMath.round(magnitude);
				accelerometerMeasures.add(new AccelerometerMeasure(
						axisX, axisY, axisZ, magnitude, (int)StrictMath.round(magnitude), resultSet.getLong(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (!accelerometerMeasures.isEmpty()) {
			String folder = "D:\\_proyectoAlz\\analysis\\accelerometer\\walking\\";
			new File(folder).mkdirs();

			try {
				String user = userID < 10 ? "0" + userID : userID + "";
				String fileNumber = file < 10 ? "0" + file : file + "";
				String filename = folder + "user" + user + "w" + fileNumber + ".xls";

				WorkbookSettings wbSetting = new WorkbookSettings();
				wbSetting.setUseTemporaryFileDuringWrite(true);
				WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), wbSetting);

				WritableFont fontNormal = new WritableFont(WritableFont.createFont("CALIBRI"), 11);
				WritableFont fontBold = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD);
				WritableFont fontRed = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
				WritableFont fontRedBig = new WritableFont(WritableFont.createFont("CALIBRI"), 16, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
				WritableFont fontBlue = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD,
						false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);

				WritableCellFormat formatNormal = new WritableCellFormat(fontNormal);
				WritableCellFormat formatNormalDecimal = new WritableCellFormat(fontNormal, new NumberFormat("0.00"));
				WritableCellFormat formatNormalDecimalAlpha = new WritableCellFormat(fontNormal, new NumberFormat("0.0"));
				WritableCellFormat formatNormalTimestamp = new WritableCellFormat(fontNormal, new NumberFormat("0"));
				WritableCellFormat formatBold = new WritableCellFormat(fontBold);
				WritableCellFormat formatBoldDecimal = new WritableCellFormat(fontBold, new NumberFormat("0.00"));
				WritableCellFormat formatRed = new WritableCellFormat(fontRed);
				WritableCellFormat formatRedBig = new WritableCellFormat(fontRedBig, new NumberFormat("0.00"));
				WritableCellFormat formatRedDecimal = new WritableCellFormat(fontRed, new NumberFormat("0.00"));
				WritableCellFormat formatBlue = new WritableCellFormat(fontBlue, new NumberFormat("0.0"));
				WritableCellFormat formatDivisor = new WritableCellFormat();
				formatDivisor.setBackground(Colour.GREY_25_PERCENT);
				WritableCellFormat formatBoldCenter = new WritableCellFormat(fontBold);
				formatBoldCenter.setAlignment(Alignment.CENTRE);

//				ArrayList<AccelerometerMeasure> accFilteredMeasures = new ArrayList<>();

				WritableSheet worksheet = workbook.createSheet("summary", 0);

				int freqForAvg = 0, counter = 0;
				long frequency, invalidFrequency = 0;
				for (int i = 1; i < accelerometerMeasures.size(); i++) {
					frequency = accelerometerMeasures.get(i).getTimestamp() -
							accelerometerMeasures.get(i - 1).getTimestamp();
					if (frequency > AccelerometerUtil.maxFrequency) {
						invalidFrequency += - AccelerometerUtil.expectedFrequency + frequency;
					}
					else {
						freqForAvg += frequency;
						counter++;
					}
				}
				long time = accelerometerMeasures.get(accelerometerMeasures.size() - 1).getTimestamp() -
						accelerometerMeasures.get(0).getTimestamp() - invalidFrequency;
				float freqAvg = (float)freqForAvg / counter;
				worksheet.addCell(new Number(7, 2, freqAvg, formatBoldDecimal));

				worksheet.addCell(new Label(0, 1, "Alpha", formatBoldCenter));
				worksheet.addCell(new Label(1, 1, "Magnitude", formatBoldCenter));
				worksheet.addCell(new Label(2, 1, "Magnitude [r]", formatBoldCenter));
				worksheet.addCell(new Label(3, 1, "Magnitude [-g]", formatBoldCenter));
				worksheet.addCell(new Label(4, 1, "Magnitude [-g][r]", formatBoldCenter));
				worksheet.addCell(new Label(5, 1, "Magnitude [-g][r][min]", formatBoldCenter));
				worksheet.addCell(new Label(7, 1, "Frequency", formatBoldCenter));

				float alpha = 0;
				for (int  i = 2; i <= 12; alpha += 0.1, i++) {
					/*
					* Time [c]
					* Invalid samples
					* Time (ms) from invalid samples
					* Time (ms) corrected for invalid samples
					* */
					AccelerometerCount.initiateGravity();
					AccelerometerCount.setAlpha(alpha);

					double accFilteredMagnitudes = 0, accFilteredMagnitudesRounded = 0;
					double accFilteredMagnitude;
					for (AccelerometerMeasure accM : accelerometerMeasures) {
						double[] accFM = AccelerometerCount.getFilteredAcceleration(
								accM.getAxisX(), accM.getAxisY(), accM.getAxisZ());
						accFilteredMagnitudes += accFilteredMagnitude = Math.sqrt(
								Math.pow(accFM[0], 2) +
								Math.pow(accFM[1], 2) +
								Math.pow(accFM[2], 2));
						accFilteredMagnitudesRounded += StrictMath.round(accFilteredMagnitude);
					}

					float freqRatio = (float) AccelerometerUtil.expectedFrequency / freqAvg;

					// add to summary
					worksheet.addCell(new Number(0, i, alphas[i - 2], formatNormalDecimalAlpha));
					worksheet.addCell(new Number(1, i, accMagnitudes / freqRatio, formatNormalDecimal));
					worksheet.addCell(new Number(2, i, accMagnitudesRounded / freqRatio, formatNormal));
					worksheet.addCell(new Number(3, i, accFilteredMagnitudes / freqRatio, formatNormalDecimal));
					worksheet.addCell(new Number(4, i, accFilteredMagnitudesRounded / freqRatio, formatNormal));

					double magnitudePerMinute = (float)accFilteredMagnitudesRounded / (((float)time / 1000) / 60);
					worksheet.addCell(new Number(5, i, magnitudePerMinute / freqRatio, formatBoldDecimal));
				}

				int[] summaryColumnWidths = new int[]{7, 14, 15, 16, 18, /*20, */22, 2, 11};

				for (int c = 0; c < summaryColumnWidths.length; c++) {
					worksheet.setColumnView(c, summaryColumnWidths[c]);
				}

				workbook.write();
				workbook.close();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	private void getDistances(double lat1, double lon1, double lat2, double lon2) {
		// Get distances with Spherical Law of Cosines
		System.out.println(GPSUtilities.getSLCDistFrom(lat1, lon1, lat2, lon2));
		// Get distances with Haversine
		System.out.println(GPSUtilities.getHaversineDistFrom(lat1, lon1, lat2, lon2));
	}

	private void saveDataFile(String filename, String row) {
		try {
			PrintWriter pw = new PrintWriter(new FileWriter(filename, true));
			pw.print(row);
			pw.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
